package Dao.Impl;

import Dao.UserDao;
import Domain.User;
import JDBCUtils.JDBCUtils;
import Service.UserService;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class UserDaoImpl implements UserDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());


    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        String sql = "select\n" +
                "    count(*)\n" +
                "from\n" +
                "  user_login e \n" +
                "join\n" +
                "  user_inf d\n" +
                "on\n" +
                "  e.id = d.id\n" +
                "where\n" +
                "  1=1";
        StringBuilder sb = new StringBuilder(sql);
        //2.遍历map
        Set<String> set = condition.keySet();
        //定义参数的集合
        List<Object> params = new ArrayList<>();
        for (String key : set) {
            //判断 排除currentPage和rows还有action (排除分页参数)
            if("currentPage".equals(key) || "rows".equals(key) || "action".equals(key)){
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            //判断value中是否有值
            if(value!=null && !"".equals(value)){
                //有值
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");//?条件的值
            }
        }
        sql=sb.toString();
        return template.queryForObject(sql,Integer.class,params.toArray());
    }

    @Override
    public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
        String sql = "select\n" +
                "    e.id , e.username,d.header , d.tel , d.address\n" +
                "from\n" +
                "  user_login e \n" +
                "join\n" +
                "  user_inf d\n" +
                "on\n" +
                "  e.id = d.id\n" +
                "where\n" +
                "  1=1";
        StringBuilder sb = new StringBuilder(sql);
        //2.遍历map
        Set<String> set = condition.keySet();
        //定义参数的集合
        List<Object> params = new ArrayList<>();
        for (String key : set) {
            //判断 排除currentPage和rows (排除分页参数)
            if("currentPage".equals(key) || "rows".equals(key) || "action".equals(key)){
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            //判断value中是否有值
            if(value!=null && !"".equals(value)){
                //有值
                sb.append(" and "+key+" like ? ");
                params.add("%"+value+"%");;//?条件的值
            }
        }
        //添加分页查询
        sb.append(" limit ?,?");
        //添加分页查询参数值
        params.add(start);
        params.add(rows);
        sql=sb.toString();

        return template.query(sql,new BeanPropertyRowMapper<>(User.class),params.toArray());
    }

    @Override
    public List<User> findUserById(int id) {
        String sql ="select\n" +
                "    e.* , d.*\n" +
                "from\n" +
                "  user_login e \n" +
                "join\n" +
                "  user_inf d\n" +
                "on\n" +
                "  e.id = d.id\n" +
                "where\n" +
                "  e.id=?";
        try {
            return template.query(sql,new BeanPropertyRowMapper<>(User.class),id);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void updateUser(User user) {
        String sql ="update\n" +
                " user_login t ,user_inf t1\n" +
                "set\n" +
                "  t.username=? , t1.header=?,t1.tel=?,t1.address=?\n" +
                "where\n" +
                "  t.id =? and t1.id=?;";
        template.update(sql,user.getUsername(),user.getHeader(),user.getTel(),user.getAddress(),user.getId(),user.getId());
    }

    @Override
    public void deleteUser(int id) {
        String sql1 = "delete \n" +
                " b\n" +
                "from\n" +
                " user_login a\n" +
                "left join\n" +
                " user_inf b\n" +
                "on\n" +
                " a.id=b.id\n" +
                "where\n" +
                " a.id=?";
        template.update(sql1,id);

        String sql2="delete from user_login where id=?";
        template.update(sql2,id);
    }

    @Override
    public List<User> UserLogin(User user) {
        String sql = "select\n" +
                "t.id , t.username , t.password , t1.header , t1.tel , t1.address , t1.c_question , t1.c_answer\n" +
                "from\n" +
                " user_login t\n" +
                "join\n" +
                " user_inf t1\n" +
                "on\n" +
                " t.id = t1.id\n" +
                "where\n" +
                " username =? and password=?";
        List<User> result = null;
        try {
            result = template.query(sql,new BeanPropertyRowMapper<>(User.class),user.getUsername(),user.getPassword());
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public void AddUser(User user) {
        String sql = "insert into user_login values(null,?,?)";
        template.update(sql,user.getUsername(),user.getPassword());

        String sql1 ="select id from user_login where username=? and password =?";
        Integer id = template.queryForObject(sql1, Integer.class, user.getUsername(), user.getPassword());
        System.out.println(id);
        String sql2 = "insert into user_inf(id,tel,header) values(?,?,?)";
        template.update(sql2,id,user.getTel(),user.getHeader());

        String sql3 = "insert into collections(id) values(?)";
        template.update(sql3,id);

        String sql4 = "insert into cart(id) values(?)";
        template.update(sql4,id);

    }

    @Override
    public List<User> FindUserByUsername(String username) {
        String sql ="select\n" +
                "t.id , t.username , t.password , t1.header , t1.tel , t1.address , t1.c_question , t1.c_answer\n" +
                "from\n" +
                " user_login t\n" +
                "join\n" +
                " user_inf t1\n" +
                "on\n" +
                " t.id = t1.id\n" +
                "where\n" +
                " username =?";
        List<User> result = null;
        try {
            result = template.query(sql,new BeanPropertyRowMapper<>(User.class),username);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public void changePassword(int id, String password) {
        String sql = "update user_login set password=? where id=?";
        template.update(sql,password,id);
    }

    @Override
    public void SetSecurity(int id, String question, String answer) {
        String sql = "update user_inf set c_question = ? , c_answer=? where id = ?";
        template.update(sql,question,answer,id);
    }

    @Override
    public void UpdateUserInf(int id, String username, String header, String tel, String address) {
        String sql = "update user_login set username=? where id=?";
        template.update(sql,username,id);

        String sql2= "update user_inf set header=?,tel=?,address=? where id=?";
        template.update(sql2,header,tel,address,id);
    }


}
